package com.weilan.ark3.sqlite;

import cn.hutool.http.HttpStatus;
import com.weilan.ark3.entity.Fault;
import com.weilan.ark3.entity.page.TableDataInfo;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;

/**
 * 故障信息从数据库
 */
public class SQLiteUtilsFault {


    /**
     * 插入故障信息的数据库
     */

    public static void insertToDB2(String tableName, String time, String description, int slaveID) {
        try {
            //连接SQLite的JDBC2
            Class.forName("org.sqlite.JDBC");
            //建立一个数据库名weilan.db的连接，如果不存在就在当前目录下创建之
            Connection conn = DriverManager.getConnection("jdbc:sqlite:C:/db/ARK3/fault.db");
            Statement stat = conn.createStatement();
            StringBuilder fileStr = new StringBuilder();
            fileStr.append("'");
            fileStr.append(time);
            fileStr.append("'");
            fileStr.append(",");
            fileStr.append(slaveID);
            fileStr.append(",");
            fileStr.append("'");
            fileStr.append(description);
            fileStr.append("'");
            //创建一个新的字符串
            String insertSql = "insert into %s values(%s);";
//            INSERT INTO "main"."fault_info"("time", "slaveID", "info") VALUES ('111111', 23, 'djsajdssjlksad');
            //插入数据
            String format = String.format(insertSql, tableName, fileStr);
            stat.executeUpdate(format);
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }


    /**
     * 查询ZL01数据库
     *
     * @return
     */
    public static TableDataInfo<Object> selectToDB2(String startTime, String endTime) {
        TableDataInfo<Object> mapTableDataInfo = new TableDataInfo<>();
        try {
            // 数据库连接信息
            Class.forName("org.sqlite.JDBC");
            //建立一个数据库名weilan.db的连接，如果不存在就在当前目录下创建之
            Connection conn = DriverManager.getConnection("jdbc:sqlite:C:/db/ARK3/fault.db");
            Statement stat = conn.createStatement();
            // SQL 查询语句
            String sqlQuery = "select * FROM fault_info where time > '%s' and time < '%s' ORDER BY time DESC,slaveID ASC";
            String format = String.format(sqlQuery, startTime, endTime);
            // 执行查询
            ResultSet resultSet = stat.executeQuery(format);
            ArrayList<Object> objects = new ArrayList<>();


//            if (resultSet.next()) {
            while (resultSet.next()) {
                Fault fault = new Fault();
                String info = resultSet.getString("info");
                String slaveID = resultSet.getString("slaveID");
                String time = resultSet.getString("time");
                fault.setDataTime(time);
                fault.setSlaveID(slaveID);
                fault.setMsg(info);
                objects.add(fault);
                mapTableDataInfo.setData(objects);
                mapTableDataInfo.setCode(HttpStatus.HTTP_OK);
                mapTableDataInfo.setMsg("查询成功");
            }
            if (objects.isEmpty()) {
                mapTableDataInfo.setData(objects);
                mapTableDataInfo.setCode(HttpStatus.HTTP_OK);
                mapTableDataInfo.setMsg("查询失败");
            }
//            } else {
//                //设置为[]
//                mapTableDataInfo.setData(new ArrayList<>());
//                mapTableDataInfo.setCode(HttpStatus.HTTP_OK);
//                mapTableDataInfo.setMsg("无故障信息");
//            }
            // 关闭连接
            resultSet.close();
            stat.close();
            conn.close();
            mapTableDataInfo.setTotal(objects.size());
            return mapTableDataInfo;

        } catch (Exception e) {
            throw new RuntimeException(e);
        }

    }
}